﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace CodeBuilder.Utility
{
    static class Database
    {
        public static List<string> GetTable()
        {
            List<string> tables = new List<string>();
            try
            {

                SqlDataReader dr = SqlHelper.ExecuteReader("Select Name FROM SysObjects Where XType='U' orDER BY Name");
                while (dr.Read())
                {
                    tables.Add((dr["name"].ToString()).Singularize());
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return tables;
        }

        public static string Singularize(this string value)
        {
            return System.Data.Entity.Design.PluralizationServices.PluralizationService.CreateService(new System.Globalization.CultureInfo("en")).Singularize(value);
        }
        public static string Pluralize(this string value)
        {
            return System.Data.Entity.Design.PluralizationServices.PluralizationService.CreateService(new System.Globalization.CultureInfo("en")).Pluralize(value);
        }

        public static List<DataTableInfo> GetTableInfo(string tableName)
        {
            List<DataTableInfo> result = new List<DataTableInfo>();

            string sql = string.Format(@"SELECT C.name as ColumnName,T.name as ColumnType,convert(bit,C.IsNullable) as IsNullable  
     ,convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end) as IsPrimary  
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as IsIdentity,C.Length as Length,COLUMNPROPERTY(C.id,C.name,'PRECISION') as Precision
     ,ISNULL(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as Scale,ISNULL(CM.text,'') as DefaultValue,ISNULL(ETP.value,'') AS Description   
    FROM syscolumns C INNER JOIN systypes T ON C.xusertype = T.xusertype    
    left JOIN sys.extended_properties ETP   ON  ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'    
    left join syscomments CM on C.cdefault=CM.id WHERE C.id = object_id('{0}')", tableName.Pluralize());
            SqlDataReader dr = SqlHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                result.Add(new DataTableInfo()
                {
                    ColumnName = dr["ColumnName"].ToString(),
                    ColumnType = dr["ColumnType"].ToString(),
                    IsNullable = (bool)dr["IsNullable"],
                    IsPrimary = (bool)dr["IsPrimary"],
                    IsIdentity = (bool)dr["IsIdentity"],
                    MaxLength = dr["Length"].ToString(),
                    Precision = dr["Precision"].ToString(),
                    Scale = (int)dr["Scale"],
                    DefaultValue = dr["DefaultValue"].ToString(),
                    Description = dr["Description"].ToString(),
                    MappingResult = dr["ColumnType"].ToString().MappTo((bool)dr["IsNullable"], (int)dr["Precision"]),
                });
            }

            return result;
        }

        public static List<Relation> GetRelation()
        {
            List<Relation> result = new List<Relation>();

            SqlDataReader dr = SqlHelper.ExecuteReader(BuilderTemplate.BuildAllRelation);
            while (dr.Read())
            {
                result.Add(new Relation()
                {
                    PrimaryKeyTable = dr["PrimaryKeyTable"].ToString(),
                    PrimaryKey = dr["PrimaryKey"].ToString(),
                    ForeignKeyTable = dr["ForeignKeyTable"].ToString(),
                    ForeignKey = dr["ForeignKey"].ToString(),
                });
            }

            return result;
        }

    }

    public class DataTableInfo
    {
        /// <summary>
        /// 列名
        /// </summary>
        public string ColumnName { get; set; }
        /// <summary>
        /// 系统数据类型
        /// </summary>
        public string ColumnType { get; set; }
        /// <summary>
        /// 转换后数据类型
        /// </summary>
        public EDMMapping.MappingResult MappingResult { get; set; }
        /// <summary>
        /// 主键
        /// </summary>
        public bool IsPrimary { get; set; }
        /// <summary>
        /// 是否允许为空
        /// </summary>
        public bool IsNullable { get; set; }
        /// <summary>
        /// 自增
        /// </summary>
        public bool IsIdentity { get; set; }
        /// <summary>
        /// 最大长度
        /// </summary>
        public string MaxLength { get; set; }
        /// <summary>
        /// 如果列包含的是数值，则为该列的精度；否则为0
        /// </summary>
        public string Precision { get; set; }
        /// <summary>
        /// 如果列包含的是数值，则为列的小数位数；否则为0
        /// </summary>
        public int Scale { get; set; }
        /// <summary>
        /// 默认值
        /// </summary>
        public string DefaultValue { get; set; }
        /// <summary>
        /// 数据库备注
        /// </summary>
        public string Description { get; set; }
    }

    public class Relation
    {
        /// <summary>
        /// 主键表名
        /// </summary>
        public string PrimaryKeyTable { get; set; }
        /// <summary>
        /// 主键ID
        /// </summary>
        public string PrimaryKey { get; set; }
        /// <summary>
        /// 外键表名
        /// </summary>
        public string ForeignKeyTable { get; set; }
        /// <summary>
        /// 外键值
        /// </summary>
        public string ForeignKey { get; set; }
    }
}
